package com.tju320.elm.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.tju320.elm.dao.BusinessDao;
import com.tju320.elm.po.Business;
import com.tju320.elm.util.DBUtil;

public class BusinessDaoImpl implements BusinessDao{

	@Override//实现模糊查询，全查询,要有一种只用一个函数就把事做了的思想，能不用重载就不用，
	//可以通过null值判断来避免重载四个版本,如果是null的话说明没有条件，有的话就用like查询
	public List<Business> listBusiness(String businessName, String businessAddress) {
		//通过列表是否为空判断是否存在这样的元素
		List<Business> list=new ArrayList<Business>();
		Connection conn = DBUtil.getConnection();
		PreparedStatement pstm = null;
		ResultSet rs = null;
		// 3.准备发送SQL,where 1=1是一个写jdbc的小技巧,省略了一些复杂的逻辑判断
		StringBuffer sql =new StringBuffer("select * from business where 1=1");
		//模糊查询的条件，通过+拼接字符串包含%实现,还有就是记得前面要加空格
		if(businessName!=null) {
			sql.append(" and businessName like "+"'%"+businessName+"%'");
		}
		if(businessAddress!=null) {
			sql.append(" and businessAddress like "+"'%"+businessAddress+"%'");
		}
		sql.append(" order by businessId ASC");
		try {
			pstm = conn.prepareStatement(sql.toString());
			// 执行sql
			rs = pstm.executeQuery();
			//可能有多个结果，装载list里返回
			while(rs.next()) {
				Business business=new Business();
				business.setBusinessAddress(rs.getString("businessAddress"));
				business.setBusinessExplain(rs.getString("businessExplain"));
				business.setBusinessId(rs.getInt("businessId"));
				business.setBusinessName(rs.getString("businessName"));
				business.setDeliveryPrice(rs.getDouble("deliveryPrice"));
				business.setPassword(rs.getString("password"));
				business.setStarPrice(rs.getDouble("starPrice"));
				list.add(business);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, pstm, conn);
		}
		
		return list;
	}
	

	@Override//只插入businessName，初始密码设置为123
	public int saveBusiness(String businessName) {
		Connection conn = DBUtil.getConnection();
		PreparedStatement pstm = null;
		ResultSet rs = null;
		int buinessId=-1;
		String sql ="insert into business (businessName,password) values (?,'123')";
		try {
			//要设置以返回自增长的主键
			pstm = conn.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
			pstm.setString(1, businessName);
//			是executeUpdate而不是executeQuery
			pstm.executeUpdate();
			//rs承接自增长的主键集合
			rs=pstm.getGeneratedKeys();
			if(rs.next()) {
				//jdbc是从1开始的
				buinessId=rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, pstm, conn);
		}
		return buinessId;
	}
	
	
	
	

	@SuppressWarnings("resource")
	@Override
	public int removeBusiness(int businessId) {
		Connection conn = DBUtil.getConnection();
		PreparedStatement pstm = null;
		ResultSet rs = null;
		int result=0;
		//要删除主表的话，也把子表的给删了，其实可以用cascade给级联删除就省去了这个操作
		String sqlDelFood ="delete from food where businessId = ?";
		String sqlDelBusi ="delete from business where businessId = ?";
		try {
			//开启事务
			conn.setAutoCommit(false);
			pstm=conn.prepareStatement(sqlDelFood);
			pstm.setInt(1, businessId);
			pstm.executeUpdate();
			
			pstm=conn.prepareStatement(sqlDelBusi);
			pstm.setInt(1, businessId);
			result=pstm.executeUpdate();
			
			conn.commit();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				System.out.println("rollback fail");
				e1.printStackTrace();
			}
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, pstm, conn);
		}
		return result;
	}
	public static void main(String[] args) {
		new BusinessDaoImpl().removeBusiness(9);
	}

	@Override
	public Business getBusinessByIdByPass(Integer businessId, String password) {
		Connection conn = DBUtil.getConnection();
		Business business=null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		String sql="select * from business where businessId=? and password=?";
		try {
			pstm=conn.prepareStatement(sql);
			pstm.setInt(1, businessId);
			pstm.setString(2, password);
			rs=pstm.executeQuery();
			if(rs.next()) {
				business=new Business();
				business.setBusinessAddress(rs.getString("businessAddress"));
				business.setBusinessExplain(rs.getString("businessExplain"));
				business.setBusinessId(rs.getInt("businessId"));
				business.setBusinessName(rs.getString("businessName"));
				business.setDeliveryPrice(rs.getDouble("deliveryPrice"));
				business.setPassword(rs.getString("password"));
				business.setStarPrice(rs.getDouble("starPrice"));
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, pstm, conn);
		}
		
		return business;
	}

	@Override
	public Business getBusinessById(Integer businessId) {
		Connection conn = DBUtil.getConnection();
		Business business=null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		String sql="select * from business where businessId=?";
		try {
			pstm=conn.prepareStatement(sql);
			pstm.setInt(1, businessId);
			rs=pstm.executeQuery();
			if(rs.next()) {
				business=new Business();
				business.setBusinessAddress(rs.getString("businessAddress"));
				business.setBusinessExplain(rs.getString("businessExplain"));
				business.setBusinessId(rs.getInt("businessId"));
				business.setBusinessName(rs.getString("businessName"));
				business.setDeliveryPrice(rs.getDouble("deliveryPrice"));
				business.setPassword(rs.getString("password"));
				business.setStarPrice(rs.getDouble("starPrice"));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, pstm, conn);
		}
		
		return business;
	}

	@Override
	public int updateBusiness(Business business) {
		Connection conn = DBUtil.getConnection();
		PreparedStatement pstm = null;
		ResultSet rs = null;
		int result=0;
		String sql="update business SET businessName=?,businessAddress=?, businessExplain=?,starPrice=?,deliveryPrice=? WHERE businessId=?";
		try {
			pstm=conn.prepareStatement(sql);
			pstm.setString(1,business.getBusinessName());
			pstm.setString(2,business.getBusinessAddress());
			pstm.setString(3,business.getBusinessExplain());
			pstm.setDouble(4,business.getStarPrice());
			pstm.setDouble(5,business.getDeliveryPrice());
			pstm.setInt(6, business.getBusinessId());
			result=pstm.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, pstm, conn);
		}
	
		return result;
	}

	@Override
	public int updateBusinessByPassword(Integer businessId, String password) {
		Connection conn = DBUtil.getConnection();
		PreparedStatement pstm = null;
		ResultSet rs = null;
		int result=0;
		String sql="update business SET password=? WHERE businessId=?";
		try {
			pstm=conn.prepareStatement(sql);
			pstm.setString(1,password);
			pstm.setInt(2, businessId);
			result=pstm.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, pstm, conn);
		}
	
		return result;
	}

}
